
--===================================================================
--===========================TABLE===================================
--===================================================================
 
	CREATE TABLE "TRANGTHAITG" 
	(	"MSTTTG" NUMBER(*,0) NOT NULL ENABLE, 
		"TENTT" NVARCHAR2(2) NOT NULL ENABLE, 
		"PHANTRAMTP" FLOAT(126) NOT NULL ENABLE, 
		 CONSTRAINT "TRANGTHAITG_PK" PRIMARY KEY ("MSTTTG") ENABLE, 
		 CONSTRAINT "TENTT" CHECK (
		REGEXP_LIKE (TENTT, '^TG|HH|DD$' )
		) ENABLE
	   );
	
	CREATE TABLE "PHIVCHH" 
	   (	"MSPHH" NUMBER(*,0) NOT NULL ENABLE, 
		"LOAIVE" NVARCHAR2(3) NOT NULL ENABLE, 
		"TRONGLUONGDM" FLOAT(126) NOT NULL ENABLE,
		"DONGIA_KG" NUMBER NOT NULL ENABLE, 
		"THOIDIEMAPDUNG" DATE NOT NULL ENABLE, 
		 CONSTRAINT "PHIVCHH_PK" PRIMARY KEY ("MSPHH") ENABLE, 
		 CONSTRAINT "LOAIVE" CHECK (
	REGEXP_LIKE (LOAIVE, '^VIP|PT|SAV$' )
	) ENABLE
	  );
	  
	CREATE TABLE "LOAIMAYBAY" 
	   (	"MSLMB" NUMBER NOT NULL ENABLE, 
		"HANGSX" NVARCHAR2(250) NOT NULL ENABLE, 
		"MODEL" NVARCHAR2(250) NOT NULL ENABLE, 
		"SOGHEVIP" NUMBER(*,0) NOT NULL ENABLE, 
		"SOGHEPT" NUMBER(*,0) NOT NULL ENABLE, 
		"TONGSOGHE" NUMBER(*,0) NOT NULL ENABLE, 
		 CONSTRAINT "LOAIMAYBAY_PK" PRIMARY KEY ("MSLMB") ENABLE
	   );
	
	
  CREATE TABLE "GHENGOI" 
   (	"MSG" NUMBER NOT NULL ENABLE, 
	"GHESO" NVARCHAR2(3) NOT NULL ENABLE, 
	"LOAIGHE" NVARCHAR2(3) NOT NULL ENABLE, 
	"MSLMB" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "GHESO" CHECK (
		REGEXP_LIKE (GHESO, '^[A-Z][0-9]{2}$' )
		) ENABLE, 
	 CONSTRAINT "LOAIGHE" CHECK (
		REGEXP_LIKE (LOAIGHE, '^VIP|PT|SAV$' )
		) ENABLE, 
	 CONSTRAINT "GHENGOI_PK" PRIMARY KEY ("MSG") ENABLE,
	 CONSTRAINT "GHENGOI_LOAIMAYBAY_FK1" FOREIGN KEY ("MSLMB")
	  REFERENCES "LOAIMAYBAY" ("MSLMB") ENABLE
   );
 
	   
	
	CREATE TABLE "GA" 
	   (	"MSG" NUMBER NOT NULL ENABLE, 
		"TENSB" NVARCHAR2(250) NOT NULL ENABLE, 
		"THANHPHO" NVARCHAR2(250) NOT NULL ENABLE, 
		"QUOCGIA" NVARCHAR2(250) NOT NULL ENABLE, 
		 CONSTRAINT "GA_PK" PRIMARY KEY ("MSG") ENABLE
	   );
	   
	
	CREATE TABLE "THUCPHAM" 
	   (	"MSTP" NUMBER NOT NULL ENABLE, 
		"TEN" NVARCHAR2(250) NOT NULL ENABLE, 
		"MOTA" NVARCHAR2(250) NOT NULL ENABLE, 
		 CONSTRAINT "THUCPHAM_PK" PRIMARY KEY ("MSTP") ENABLE
	   );
	   
	   
	   
	CREATE TABLE "TUYENBAY" 
   (	"MSTB" NUMBER NOT NULL ENABLE, 
	"MSG_DI" NUMBER NOT NULL ENABLE, 
	"MSG_DEN" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "TUYENBAY_PK" PRIMARY KEY ("MSTB") ENABLE, 
	 CONSTRAINT "TUYENBAY_GA_FK1" FOREIGN KEY ("MSG_DI")
	  REFERENCES "GA" ("MSG") ENABLE, 
	 CONSTRAINT "TUYENBAY_GA_FK2" FOREIGN KEY ("MSG_DEN")
	  REFERENCES "GA" ("MSG") ENABLE
	);
  
  
	
	  CREATE TABLE "MAYBAY" 
	   (	"MSMB" NVARCHAR2(6) DEFAULT 0 NOT NULL ENABLE, 
		"TONGSOGIOBAY" FLOAT(126) DEFAULT 0 NOT NULL ENABLE, 
		"NAMSX" NUMBER NOT NULL ENABLE, 
		"THOIDIEMSD" DATE NOT NULL ENABLE, 
		"MSLMB" NUMBER NOT NULL ENABLE, 
		 CONSTRAINT "MSMB" CHECK (
		REGEXP_LIKE (MSMB, '^SAP[0-9]{3}$' )
		) ENABLE, 
		 CONSTRAINT "MAYBAY_PK" PRIMARY KEY ("MSMB") ENABLE, 
		 CONSTRAINT "MAYBAY_LOAIMAYBAY_FK1" FOREIGN KEY ("MSLMB")
		  REFERENCES "LOAIMAYBAY" ("MSLMB") ENABLE
	   );




	  CREATE TABLE "CHUYENBAY" 
	   (	"MSCB" NVARCHAR2(9) NOT NULL ENABLE, 
		"TRANGTHAI" NVARCHAR2(20) NOT NULL ENABLE, 
		"SOGHETRONG" NUMBER(*,0) NOT NULL ENABLE, 
		"THOIDIEMDI" TIMESTAMP (6) NOT NULL ENABLE, 
		"THOIDIEMDEN" TIMESTAMP (6) NOT NULL ENABLE, 
		"MSMB" NVARCHAR2(6) NOT NULL ENABLE, 
		"MSTB" NUMBER(*,0) NOT NULL ENABLE, 
		"ECONOMY" NUMBER, 
		"BUSINESS" NUMBER, 
		"SAVER_FLEX" NUMBER, 
		"RAND_CODE" NVARCHAR2(10), 
		 CONSTRAINT "CHUYENBAY_PK" PRIMARY KEY ("MSCB") ENABLE, 
		 CONSTRAINT "TRANGTHAI" CHECK (
		REGEXP_LIKE (TRANGTHAI, '^CB|DB|HB$' )
		) ENABLE, 
		 CONSTRAINT "MSCB" CHECK (
		REGEXP_LIKE (MSCB, '^SA[0-9]{7}$' )
		) ENABLE, 
		 CONSTRAINT "CHUYENBAY_TUYENBAY_FK1" FOREIGN KEY ("MSTB")
		  REFERENCES "TUYENBAY" ("MSTB") ENABLE, 
		 CONSTRAINT "CHUYENBAY_MAYBAY_FK1" FOREIGN KEY ("MSMB")
		  REFERENCES "MAYBAY" ("MSMB") ENABLE
	   );
 

	  CREATE TABLE "KHACHHANG" 
	   (	"MSKH" NVARCHAR2(12) NOT NULL ENABLE, 
		"HOTEN" NVARCHAR2(250) NOT NULL ENABLE, 
		"NGAYSINH" DATE NOT NULL ENABLE, 
		"GIOITINH" NVARCHAR2(3) NOT NULL ENABLE, 
		"QUOCTICH" NVARCHAR2(20) NOT NULL ENABLE, 
		"SODT" NVARCHAR2(20) NOT NULL ENABLE, 
		"DIACHI" NVARCHAR2(250) NOT NULL ENABLE, 
		"KHOILUONGVUOT" FLOAT(126) DEFAULT 0 NOT NULL ENABLE,
		"MSTTTG" NUMBER DEFAULT 1 NOT NULL ENABLE, 
		"MSPHH" NUMBER NOT NULL ENABLE, 
		"MSCB" NVARCHAR2(9) NOT NULL ENABLE, 
		"PASSWORD" NVARCHAR2(10) NOT NULL ENABLE,
		"RAND_CODE" NVARCHAR2(20),
		 CONSTRAINT "SDT" CHECK (
	REGEXP_LIKE (SODT, '^\+84[0-9]{9}[0-9]?$' )
	) ENABLE, 
		 CONSTRAINT "KHACHHANG_PK" PRIMARY KEY ("MSKH") ENABLE, 
		 CONSTRAINT "GT" CHECK (
	REGEXP_LIKE (GIOITINH, '^Nam|Nu$' )
	) ENABLE, 
		 CONSTRAINT "MSKH" CHECK (
	REGEXP_LIKE (MSKH, '^NL|TE[0-9]{0,10}$' )
	) ENABLE, 
		 CONSTRAINT "KHACHHANG_TRANGTHAITG_FK1" FOREIGN KEY ("MSTTTG")
		  REFERENCES "TRANGTHAITG" ("MSTTTG") ENABLE, 
		 CONSTRAINT "KHACHHANG_CHUYENBAY_FK1" FOREIGN KEY ("MSCB")
		  REFERENCES "CHUYENBAY" ("MSCB") ENABLE, 
		 CONSTRAINT "KHACHHANG_PHIVCHH_FK1" FOREIGN KEY ("MSPHH")
		  REFERENCES "PHIVCHH" ("MSPHH") ENABLE
	   );
 

	  
  CREATE TABLE "KHACHHANGNL" 
   (	"CMND" NVARCHAR2(20) NOT NULL ENABLE, 
	"PASSPORT" NVARCHAR2(20), 
	"MSKH" NVARCHAR2(12) NOT NULL ENABLE, 
	 CONSTRAINT "KHACHHANGNL_PK" PRIMARY KEY ("MSKH") ENABLE, 
	 CONSTRAINT "KHACHHANG_NL" CHECK (
REGEXP_LIKE (MSKH, '^NL[0-9]{10}$' )
) ENABLE, 
	 CONSTRAINT "KHACHHANGNL_KHACHHANG_FK1" FOREIGN KEY ("MSKH")
	  REFERENCES "KHACHHANG" ("MSKH") ENABLE
   );
 

 

	  
  CREATE TABLE "KHACHHANGTE" 
   (	"THONGTINKSINH" NVARCHAR2(500), 
	"MSKH" NVARCHAR2(12) NOT NULL ENABLE, 
	"MSNGH" NVARCHAR2(12) NOT NULL ENABLE, 
	 CONSTRAINT "KHACHHANGTE_PK" PRIMARY KEY ("MSKH")
  ENABLE, 
	 CONSTRAINT "KHACHHANG_TE" CHECK (
REGEXP_LIKE (MSKH, '^TE[0-9]{10}$' )
) ENABLE, 
	 CONSTRAINT "KHACHHANGTE_KHACHHANG_FK1" FOREIGN KEY ("MSKH")
	  REFERENCES "KHACHHANG" ("MSKH") ENABLE, 
	 CONSTRAINT "KHACHHANGTE_KHACHHANGNL_FK1" FOREIGN KEY ("MSNGH")
	  REFERENCES "KHACHHANGNL" ("MSKH") ENABLE
   );
 

 
	
  CREATE TABLE "GHEKHACH" 
   (	"MSKH" NVARCHAR2(12) NOT NULL ENABLE, 
	"GHESO" NVARCHAR2(3) NOT NULL ENABLE, 
	"GIA" NUMBER NOT NULL ENABLE, 
	"MSCB" NVARCHAR2(9) NOT NULL ENABLE, 
	 CONSTRAINT "GHESO_KH" CHECK (
		REGEXP_LIKE (GHESO, '^[A-Z][0-9]{2}$' )
) ENABLE, 
	 CONSTRAINT "GHEKHACH_PK" PRIMARY KEY ("MSKH", "GHESO"), 
	 CONSTRAINT "GHEKHACH_UK1" UNIQUE ("GHESO", "MSCB"), 
	 CONSTRAINT "GHEKHACH_KHACHHANG_FK1" FOREIGN KEY ("MSKH")
	  REFERENCES "KHACHHANG" ("MSKH") ENABLE, 
	 CONSTRAINT "GHEKHACH_CHUYENBAY_FK1" FOREIGN KEY ("MSCB")
	  REFERENCES "CHUYENBAY" ("MSCB") ENABLE
   );

	
  CREATE TABLE "GIATHUCPHAM" 
   (	"MSGTP" NUMBER(20,0) NOT NULL ENABLE, 
	"GIA" NUMBER(20,0) NOT NULL ENABLE, 
	"NGAYAPDUNG" DATE NOT NULL ENABLE, 
	"MSTP" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "GIATHUCPHAM_PK" PRIMARY KEY ("MSGTP") ENABLE, 
	 CONSTRAINT "GIATHUCPHAM_THUCPHAM_FK1" FOREIGN KEY ("MSTP")
	  REFERENCES "THUCPHAM" ("MSTP") ENABLE
   );
	
	
	
  CREATE TABLE "CHUYENBAYTHUCPHAM" 
   (	"MSCB" NVARCHAR2(9) NOT NULL ENABLE, 
	"MSTP" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "CHUYENBAYTHUCPHAM_PK" PRIMARY KEY ("MSCB", "MSTP") ENABLE, 
	 CONSTRAINT "CHUYENBAYTHUCPHAM_CHUYENB_FK1" FOREIGN KEY ("MSCB")
	  REFERENCES "CHUYENBAY" ("MSCB") ENABLE, 
	 CONSTRAINT "CHUYENBAYTHUCPHAM_THUCPHA_FK1" FOREIGN KEY ("MSTP")
	  REFERENCES "THUCPHAM" ("MSTP") ENABLE
   );
 

   
   
   
  CREATE TABLE "NHANVIEN" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"HOTEN" NVARCHAR2(40) NOT NULL ENABLE, 
	"NGAYSINH" DATE NOT NULL ENABLE, 
	"GIOITINH" NVARCHAR2(3) NOT NULL ENABLE, 
	"QUOCTICH" NVARCHAR2(40) NOT NULL ENABLE, 
	"CMND" NVARCHAR2(40) NOT NULL ENABLE, 
	"PASSPORT" NVARCHAR2(40), 
	"NGAYVAOLAM" DATE NOT NULL ENABLE, 
	"DIACHI" NVARCHAR2(100) NOT NULL ENABLE, 
	"SODT" NVARCHAR2(20) NOT NULL ENABLE, 
	"TIENLUONG" NUMBER(10,0) NOT NULL ENABLE, 
	"PASSWORD" NVARCHAR2(10) NOT NULL ENABLE,
	 CONSTRAINT "NHANVIEN_PK" PRIMARY KEY ("MSNV") ENABLE, 
	 CONSTRAINT "GIOITINH" CHECK (
REGEXP_LIKE (GIOITINH, '^Nam|Nu$' )
) ENABLE, 
	 CONSTRAINT "MSNV" CHECK (
REGEXP_LIKE (MSNV, '^PC|TV|KT|DH|KS[0-9]{0,10}$' )
) ENABLE, 
	 CONSTRAINT "NHANVIEN_UK1" UNIQUE ("CMND") ENABLE, 
	 CONSTRAINT "SDTNV" CHECK (
REGEXP_LIKE (SODT, '^\+84[0-9]{9}[0-9]?$' )
) ENABLE
   );
 


   
   
  CREATE TABLE "BANGCAP" 
   (	"MSBC" NUMBER(20,0) NOT NULL ENABLE, 
	"TENBANGCAP" NVARCHAR2(40) NOT NULL ENABLE, 
	"TRUONGDAOTAO" NVARCHAR2(200) NOT NULL ENABLE, 
	"NAMDAT" NUMBER(10,0) NOT NULL ENABLE, 
	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	 CONSTRAINT "BANGCAP_PK" PRIMARY KEY ("MSBC") ENABLE, 
	 CONSTRAINT "BANGCAP_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE
   );

   
   
  
  CREATE TABLE "PHICONG" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"LOAIPHICONG" NVARCHAR2(2) NOT NULL ENABLE, 
	 CONSTRAINT "PHICONG_PK" PRIMARY KEY ("MSNV") ENABLE, 
	 CONSTRAINT "LOAIPHICONG" CHECK (
REGEXP_LIKE (LOAIPHICONG, '^PL|CT$' )
) ENABLE, 
	 CONSTRAINT "MSNV_PHICONG" CHECK (
REGEXP_LIKE (MSNV, '^PC[0-9]{10}$' )
) ENABLE, 
	 CONSTRAINT "PHICONG_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE
   );
 


 

   
   
  
  CREATE TABLE "TIEPVIEN" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"NGOAINGUTHONGTHAO" NVARCHAR2(200) NOT NULL ENABLE, 
	 CONSTRAINT "TIEPVIEN_PK" PRIMARY KEY ("MSNV") ENABLE, 
	 CONSTRAINT "MSNV_TIEPVIEN" CHECK (
REGEXP_LIKE (MSNV, '^TV[0-9]{10}$' )
) ENABLE, 
	 CONSTRAINT "TIEPVIEN_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE
   );

 

   
   
  CREATE TABLE "CHINHANH" 
   (	"MSCN" NUMBER(10,0) NOT NULL ENABLE, 
	"TENCHINHANH" NVARCHAR2(40) NOT NULL ENABLE, 
	"THANHPHO" NVARCHAR2(40) NOT NULL ENABLE, 
	"QUOCGIA" NVARCHAR2(40) NOT NULL ENABLE, 
	 CONSTRAINT "CHINHANH_PK" PRIMARY KEY ("MSCN") ENABLE
   );
 
   
   
  
  CREATE TABLE "CALAMVIEC" 
   (	"MSCLV" NVARCHAR2(2) NOT NULL ENABLE, 
	"TUGIO" NUMBER(2,0) NOT NULL ENABLE, 
	"DENGIO" NUMBER(2,0) NOT NULL ENABLE, 
	 CONSTRAINT "CALAMVIEC_PK" PRIMARY KEY ("MSCLV") ENABLE,
	 CONSTRAINT "MSCLV" CHECK (
	REGEXP_LIKE (MSCLV, '^C[1-3]$' )
	) ENABLE,
	 CONSTRAINT "TUGIO" CHECK (
	TUGIO<24 AND TUGIO >=0
	) ENABLE, 
		 CONSTRAINT "DENGIO" CHECK (
	DENGIO<25 AND DENGIO>=0
	) ENABLE
	   );
 

 

   
   
   
  
  CREATE TABLE "NHANVIENMD" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"MSCN" NUMBER(10,0) NOT NULL ENABLE, 
	"MSNV_TRUONG" NVARCHAR2(12) NOT NULL ENABLE, 
	 CONSTRAINT "NHANVIENMD_PK" PRIMARY KEY ("MSNV")ENABLE, 
	 CONSTRAINT "MSNV_MD" CHECK (
REGEXP_LIKE (MSNV, '^KT|DH|KS[0-9]{10}$' )
) ENABLE, 
	 CONSTRAINT "NHANVIENMD_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE, 
	 CONSTRAINT "NHANVIENMD_NHANVIEN_FK2" FOREIGN KEY ("MSNV_TRUONG")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE, 
	 CONSTRAINT "NHANVIENMD_CHINHANH_FK1" FOREIGN KEY ("MSCN")
	  REFERENCES "CHINHANH" ("MSCN") ENABLE
   );
 

 

 
 
 
  
  
  CREATE TABLE "NVMATDAT_CALV" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"MSCLV" NVARCHAR2(2) NOT NULL ENABLE, 
	"NGAYBATDAU" DATE NOT NULL ENABLE, 
	 CONSTRAINT "NVMATDAT_CALV_PK" PRIMARY KEY ("MSNV", "MSCLV") ENABLE, 
	 CONSTRAINT "NVMATDAT_CALV_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE, 
	 CONSTRAINT "NVMATDAT_CALV_CALAMVIEC_FK1" FOREIGN KEY ("MSCLV")
	  REFERENCES "CALAMVIEC" ("MSCLV") ENABLE
   );

   
   
   
   
  
  CREATE TABLE "VANHANH" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"MSCB" NVARCHAR2(9) NOT NULL ENABLE, 
	 CONSTRAINT "VANHANH_PK" PRIMARY KEY ("MSNV", "MSCB")ENABLE, 
	 CONSTRAINT "VANHANH_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE, 
	 CONSTRAINT "VANHANH_CHUYENBAY_FK1" FOREIGN KEY ("MSCB")
	  REFERENCES "CHUYENBAY" ("MSCB") ENABLE
   );
 

 

 
 
 
  
  
  CREATE TABLE "LAI" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"MSLMB" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "LAI_PK" PRIMARY KEY ("MSNV", "MSLMB")ENABLE, 
	 CONSTRAINT "MSMV_LAI" CHECK (
REGEXP_LIKE (MSNV, '^PC[0-9]{10}$' )
) ENABLE, 
	 CONSTRAINT "LAI_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE, 
	 CONSTRAINT "LAI_LOAIMAYBAY_FK1" FOREIGN KEY ("MSLMB")
	  REFERENCES "LOAIMAYBAY" ("MSLMB") ENABLE
   );
 

 

 
 
 
  
  
  CREATE TABLE "KIEMTRA" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"MSCB" NVARCHAR2(9) NOT NULL ENABLE, 
	 CONSTRAINT "KIEMTRA_PK" PRIMARY KEY ("MSNV", "MSCB") ENABLE, 
	 CONSTRAINT "MSNV_KIEMTRA" CHECK (
REGEXP_LIKE (MSNV, '^KT[0-9]{10}$' )
) ENABLE, 
	 CONSTRAINT "KIEMTRA_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE, 
	 CONSTRAINT "KIEMTRA_CHUYENBAY_FK1" FOREIGN KEY ("MSCB")
	  REFERENCES "CHUYENBAY" ("MSCB") ENABLE
   );
 

 

 
 
 
  
  
  CREATE TABLE "CHUYENMONBD" 
   (	"MSNV" NVARCHAR2(12) NOT NULL ENABLE, 
	"MSLMB" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "CHUYENMONBD_PK" PRIMARY KEY ("MSNV", "MSLMB") ENABLE, 
	 CONSTRAINT "MSNV_BD" CHECK (
	REGEXP_LIKE (MSNV, '^KT[0-9]{10}$' )
	) ENABLE, 
	 CONSTRAINT "CHUYENMONBD_NHANVIEN_FK1" FOREIGN KEY ("MSNV")
	  REFERENCES "NHANVIEN" ("MSNV") ENABLE, 
	 CONSTRAINT "CHUYENMONBD_LOAIMAYBAY_FK1" FOREIGN KEY ("MSLMB")
	  REFERENCES "LOAIMAYBAY" ("MSLMB") ENABLE
   );
 

 

  
--====================================================================
--===========================SEQUENCE=================================
--====================================================================


   CREATE SEQUENCE  "AI_MSG"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
 

   CREATE SEQUENCE  "AI_MSGA"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
 

   CREATE SEQUENCE  "AI_MSTTTG"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
 

   CREATE SEQUENCE  "AI_MSKH"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
 

   CREATE SEQUENCE  "AI_MSLMB"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
 

   CREATE SEQUENCE  "AI_MSPHH"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
 

   CREATE SEQUENCE  "AI_MSTB"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
 

   CREATE SEQUENCE  "AI_MSTP"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
   
   CREATE SEQUENCE  "AI_MSGTP"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
   
   CREATE SEQUENCE  "AI_MSNV"  MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
   
   CREATE SEQUENCE  "AI_MSBC"  MINVALUE 1 MAXVALUE 99999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;

   CREATE SEQUENCE  "AI_MSCN"  MINVALUE 1 MAXVALUE 999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
   
   CREATE SEQUENCE  "AI_MSCB"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE ;
--===================================================================
--===========================TRIGGER=================================
--===================================================================	


	CREATE OR REPLACE TRIGGER "MAKE_MSPHH" 
	BEFORE INSERT ON PHIVCHH
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSPHH:=AI_MSPHH.nextval;
	END;
	/
	ALTER TRIGGER "MAKE_MSPHH" ENABLE;
 
 
	CREATE OR REPLACE TRIGGER "MAKE_MSTP" 
	BEFORE INSERT ON THUCPHAM
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSTP:=AI_MSTP.nextval;
	END;
	/
	ALTER TRIGGER "MAKE_MSTP" ENABLE;
 
	 CREATE OR REPLACE TRIGGER "MAKE_MSTB" 
	BEFORE INSERT ON TUYENBAY
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSTB:=AI_MSTB.nextval;
	END;
	/
	ALTER TRIGGER "MAKE_MSTB" ENABLE;
	
	CREATE OR REPLACE TRIGGER "MAKE_MSGA" 
	BEFORE INSERT ON GA
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSG:=AI_MSGA.nextval;
	END;
	/
	ALTER TRIGGER "MAKE_MSGA" ENABLE;

	CREATE OR REPLACE TRIGGER "MAKE_MSLMB" 
	BEFORE INSERT ON LOAIMAYBAY
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSLMB:=AI_MSLMB.nextval;
	END;
	/
	ALTER TRIGGER "MAKE_MSLMB" ENABLE;
	
	
	CREATE OR REPLACE TRIGGER "MAKE_MSTTTG" 
	BEFORE INSERT ON TRANGTHAITG
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSTTTG:=AI_MSTTTG.nextval;
	END;
	/
	ALTER TRIGGER "MAKE_MSTTTG" ENABLE;
  
	CREATE OR REPLACE TRIGGER "MAKE_MSG" 
	BEFORE INSERT ON GHENGOI
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSG:=AI_MSG.nextval;
	END;
	/
	ALTER TRIGGER "MAKE_MSG" ENABLE;

	CREATE OR REPLACE TRIGGER "UPDATE_MSKH" 
	BEFORE UPDATE OF MSKH ON KHACHHANG
	REFERENCING OLD AS old NEW AS new 
	FOR EACH ROW 
	 WHEN (length(new.MSKH)<12) BEGIN
	  RAISE_APPLICATION_ERROR(-20101, 'MSKH is constraints [NL|TE][0-9]{10}.');
	  ROLLBACK;
	END;
	/
	ALTER TRIGGER "UPDATE_MSKH" ENABLE;
	 

	  
  CREATE OR REPLACE TRIGGER "MAKE_MSKH" 
	BEFORE INSERT ON KHACHHANG
	REFERENCING NEW AS new
	FOR EACH ROW
	  WHEN (SUBSTR(new.MSKH,0,2)='NL' or SUBSTR(new.MSKH,0,2)='TE') DECLARE
	  num NVARCHAR2(10);
	BEGIN
		num:=TO_CHAR(AI_MSKH.nextval);
		:new.MSKH := SUBSTR(:new.MSKH,0,2)||LPAD(num,10,'0');
	END;
	

	/
	ALTER TRIGGER "MAKE_MSKH" ENABLE;
 


	CREATE OR REPLACE TRIGGER "TONGSOGHE" 
	BEFORE INSERT OR UPDATE OF TONGSOGHE,SOGHEVIP,SOGHEPT, MODEL ON LOAIMAYBAY
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.TONGSOGHE:=:new.SOGHEVIP+:new.SOGHEPT;
	END;
	/
	ALTER TRIGGER "TONGSOGHE" ENABLE;
	

	 


	 


	 

	--  CREATE OR REPLACE TRIGGER "MAKE_TIMESTAMP_CHUYEBAY" 
--	BEFORE INSERT OR UPDATE OF THOIDIEMDEN,THOIDIEMDI ON CHUYENBAY
--	FOR EACH ROW 
--	BEGIN
--	  :NEW.THOIDIEMDI:=TO_TIMESTAMP(:NEW.THOIDIEMDI, 'DD/MM/YYYY HH24:MI');
--	  :NEW.THOIDIEMDEN:=TO_TIMESTAMP(:NEW.THOIDIEMDEN, 'DD/MM/YYYY HH24:MI');
--	END;
--	/
--	ALTER TRIGGER "MAKE_TIMESTAMP_CHUYEBAY" ENABLE;

	  
		  
		  CREATE OR REPLACE TRIGGER "TONGGIOBAY_MAYBAY" 
				BEFORE INSERT OR UPDATE OF TONGSOGIOBAY ON MAYBAY
				FOR EACH ROW
			  DECLARE 
				  sumGB NUMBER;
				BEGIN
			if (:new.namsx>=100000) THEN
			  :new.NAMSX:=:new.NAMSX-100000;
			else
				  SELECT SUM(EXTRACT (DAY FROM thoidiemden - thoidiemdi)*24+EXTRACT (HOUR FROM thoidiemden - thoidiemdi)+EXTRACT (MINUTE FROM thoidiemden - thoidiemdi)/60) INTO sumGB FROM chuyenbay WHERE (MSMB=:new.MSMB and trangthai='DB');
				  :new.TONGSOGIOBAY:=nvl(sumGB,0);
			end if;
				END;
	

			/
			ALTER TRIGGER "TONGGIOBAY_MAYBAY" ENABLE;
 

		 

		  CREATE OR REPLACE TRIGGER "TONGGIOBAY_CHUYENBAY" BEFORE INSERT OR UPDATE OF THOIDIEMDEN,THOIDIEMDI,TRANGTHAI ON CHUYENBAY
		FOR EACH ROW
		DECLARE
			sumGB FLOAT;
			nam_sx number;
				OLD_HOUR FLOAT;
				NEW_HOUR FLOAT;
		BEGIN
			IF (:NEW.trangthai='DB' OR :old.trangthai='DB')
				  THEN  
				SELECT TONGSOGIOBAY, namsx INTO sumGB, nam_sx 
				FROM maybay
				WHERE (MSMB=:new.MSMB);
				
				IF (:NEW.trangthai=:old.trangthai) THEN
				
					OLD_HOUR:=EXTRACT (DAY FROM :old.thoidiemden - :old.thoidiemdi)*24+EXTRACT (HOUR FROM :old.thoidiemden - :old.thoidiemdi)+EXTRACT (MINUTE FROM :old.thoidiemden - :old.thoidiemdi)/60;
					NEW_HOUR:=EXTRACT (DAY FROM :new.thoidiemden - :new.thoidiemdi)*24+EXTRACT (HOUR FROM :new.thoidiemden - :new.thoidiemdi)+EXTRACT (MINUTE FROM :new.thoidiemden - :new.thoidiemdi)/60;
				
					sumGB:=sumGB-OLD_HOUR;
					sumGB:=sumGB+NEW_HOUR;
				ELSE
					IF (:OLD.TRANGTHAI='DB') 
					THEN 
					  OLD_HOUR:=EXTRACT (DAY FROM :old.thoidiemden - :old.thoidiemdi)*24+EXTRACT (HOUR FROM :old.thoidiemden - :old.thoidiemdi)+EXTRACT (MINUTE FROM :old.thoidiemden - :old.thoidiemdi)/60;
					  sumGB:=sumGB-OLD_HOUR;
					ELSE 
					  NEW_HOUR:=EXTRACT (DAY FROM :new.thoidiemden - :new.thoidiemdi)*24+EXTRACT (HOUR FROM :new.thoidiemden - :new.thoidiemdi)+EXTRACT (MINUTE FROM :new.thoidiemden - :new.thoidiemdi)/60;
					  sumGB:=sumGB+NEW_HOUR;
					END IF;
				END IF;
				
				UPDATE maybay
				SET tongsogiobay=nvl(sumGB,0), namsx=nam_sx+100000
				WHERE MSMB=:new.MSMB;
				  END IF;
		END;


		/
		ALTER TRIGGER "TONGGIOBAY_CHUYENBAY" ENABLE;

 

  CREATE OR REPLACE TRIGGER "MAKE_MSCN" 
	BEFORE INSERT ON  ChiNhanh
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSCN:=AI_MSCN.nextval;
	END;
/
ALTER TRIGGER "MAKE_MSCN" ENABLE;
 

  CREATE OR REPLACE TRIGGER "MAKE_MSGTP" 
	BEFORE INSERT ON GiaThucPham
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSGTP:=AI_MSGTP.nextval;
	END;
	
/
ALTER TRIGGER "MAKE_MSGTP" ENABLE;
 

  CREATE OR REPLACE TRIGGER "MAKE_MSNV" BEFORE INSERT ON NHANVIEN
	FOR EACH ROW
  DECLARE
    code NVARCHAR2(2);
	  num NVARCHAR2(10);
	BEGIN
    code:=SUBSTR(:new.MSNV,0,2);
    if (code='PC' or code='TV' or code='KT' or code='DH' or code='KS') then
      num:=TO_CHAR(AI_MSNV.nextval);
      :new.MSNV := code||LPAD(num,10,'0');
    end if;
	END;
	

/
ALTER TRIGGER "MAKE_MSNV" ENABLE;
 

  CREATE OR REPLACE TRIGGER "UPDATE_MSNV" 
BEFORE UPDATE OF MSNV ON NHANVIEN
FOR EACH ROW 
 WHEN (length(new.MSNV)<12) BEGIN
	  RAISE_APPLICATION_ERROR(-20102, 'MSNV is constraints [PC|TV|KT|DH|KS][0-9]{10}.');
	  ROLLBACK;
END;
/
ALTER TRIGGER "UPDATE_MSNV" ENABLE;
 
 
 
CREATE OR REPLACE TRIGGER "MAKE_PASS_KHACHHANG" 
BEFORE INSERT ON KHACHHANG
FOR EACH ROW 
Declare
pass NVARCHAR2(10);
BEGIN
  select dbms_random.string('U', 10) str into pass from dual;
  :new.password:=pass;
END;


/
ALTER TRIGGER "MAKE_PASS_KHACHHANG" ENABLE;
 


CREATE OR REPLACE TRIGGER "MAKE_PASS_NHANVIEN" 
BEFORE INSERT ON NHANVIEN
FOR EACH ROW 
Declare
pass NVARCHAR2(10);
BEGIN
  select dbms_random.string('U', 10) str into pass from dual;
  :new.password:=pass;
END;

/
ALTER TRIGGER "MAKE_PASS_NHANVIEN" ENABLE;
 

  CREATE OR REPLACE TRIGGER "MAKE_SOGETRONG" 
BEFORE INSERT OR UPDATE OF SOGHETRONG ON CHUYENBAY
FOR EACH ROW 
DECLARE
  SOGHEDAT NUMBER;
  TONG_GHE NUMBER;
BEGIN
  IF (:NEW.SOGHETRONG<100000000) THEN
    SELECT COUNT(*) INTO SOGHEDAT FROM KHACHHANG WHERE MSCB=:NEW.MSCB;
    SELECT TONGSOGHE INTO TONG_GHE FROM LOAIMAYBAY WHERE MSLMB=(SELECT MSLMB FROM MAYBAY WHERE MSMB=:NEW.MSMB);
    :NEW.SOGHETRONG:=TONG_GHE-SOGHEDAT;
  ELSE
    :NEW.SOGHETRONG:=:NEW.SOGHETRONG-100000000;
  END IF;
END;


/
ALTER TRIGGER "MAKE_SOGETRONG" ENABLE;

  CREATE OR REPLACE TRIGGER "MAKE_MSBC" 
	BEFORE INSERT ON BangCap
	REFERENCING NEW AS new 
	FOR EACH ROW 
	BEGIN
	  :new.MSBC:=AI_MSBC.nextval;
	END;
/
ALTER TRIGGER "MAKE_MSBC" ENABLE;

  CREATE OR REPLACE TRIGGER "SOGHETRONG_KHACHHANG" 
BEFORE INSERT OR UPDATE OF MSTTTG ON KHACHHANG
FOR EACH ROW
  DECLARE
   GHETRONG NUMBER;
   
BEGIN
  SELECT SOGHETRONG INTO GHETRONG FROM CHUYENBAY WHERE MSCB=:NEW.MSCB;
  IF (:NEW.MSTTTG=1) THEN
    GHETRONG:=GHETRONG-1;
    IF ghetrong<0 THEN
      raise_application_error(1221,'insert khong thanh cong');
      ROLLBACK;
    END IF;
  ELSE
    GHETRONG:=GHETRONG+1;
  END IF;
  
  UPDATE CHUYENBAY SET SOGHETRONG=GHETRONG+100000000 WHERE MSCB=:NEW.MSCB;
END;


/
ALTER TRIGGER "SOGHETRONG_KHACHHANG" ENABLE;

  
  CREATE OR REPLACE TRIGGER "SOGHETRONG_DELETE" 
BEFORE DELETE ON KHACHHANG
FOR EACH ROW 
  DECLARE
   GHETRONG NUMBER;
   
BEGIN
  SELECT SOGHETRONG INTO GHETRONG FROM CHUYENBAY WHERE MSCB=:OLD.MSCB;
  GHETRONG:=GHETRONG+1;
  UPDATE CHUYENBAY SET SOGHETRONG=GHETRONG+100000000 WHERE MSCB=:OLD.MSCB;
END;


/
ALTER TRIGGER "SOGHETRONG_DELETE" ENABLE;
  
CREATE OR REPLACE TRIGGER "MSKE_MSCB" 
BEFORE INSERT ON CHUYENBAY
FOR EACH ROW 
BEGIN
  :new.MSCB:='SA'||LPAD(TO_CHAR(AI_MSCB.nextval),7,'0');
END;


/
ALTER TRIGGER "MSKE_MSCB" ENABLE;
  
--===================================================================
--===========================Function================================
--===================================================================	
  
  
  CREATE OR REPLACE FUNCTION "DONGIA_KLVUOT" 
  ( id_khachhang IN NVARCHAR2
  ) RETURN NUMBER AS
  KL_VUOT FLOAT;
  ID_PHIHH NUMBER;
  DONGIA NUMBER;
  BEGIN
    SELECT KHOILUONGVUOT,MSPHH INTO kl_vuot, id_phihh FROM KHACHHANG WHERE MSKH= id_khachhang;
    SELECT DONGIA_KG INTO DONGIA FROM PHIVCHH WHERE MSPHH=ID_PHIHH;
    RETURN KL_VUOT*DONGIA;
  END DONGIA_KLVUOT;
  /
 
--===================================================================
--===========================Procedures==============================
--===================================================================	
	
	
	CREATE OR REPLACE PROCEDURE "UPDATE_MSPHH" 
	( ID_KH IN NVARCHAR2
	, LOAI_VE IN NVARCHAR2
	, KETQUA OUT NVARCHAR2
	) AS
	ID_PHIVC NUMBER;
	BEGIN
	  IF (LOAI_VE='VIP' OR loai_ve='PT') THEN
		SELECT MSPHH INTO ID_PHIVC FROM (SELECT MSPHH FROM PHIVCHH WHERE LOAIVE=LOAI_VE ORDER BY thoidiemapdung DESC) WHERE rownum<=1;
		UPDATE KHACHHANG SET MSPHH=ID_PHIVC WHERE MSKH=ID_KH;
		KETQUA:='CAP NHAT THANH CONG';  
	  ELSE
		KETQUA:='LOAI VE LA "VIP" HOAC "PT"';
	  END IF;
	END UPDATE_MSPHH;
	/
 
 


  CREATE OR REPLACE PROCEDURE "UPDATE_KLVUOT" 
  ( id_khachhang IN NVARCHAR2
  , KL_HH IN FLOAT
  , PHI_VC OUT NUMBER
  ) AS
  KL_dm FLOAT;
  BEGIN
      SELECT trongluongdm INTO kl_dm FROM PHIVCHH WHERE MSPHH= (SELECT MSPHH FROM khachhang WHERE mskh= id_khachhang);
      IF kl_hh> kl_dm THEN
        UPDATE KHACHHANG SET KHOILUONGVUOT= kl_hh- kl_dm WHERE MSKH= id_khachhang;
        PHI_VC:=DONGIA_KLVUOT(id_khachhang);
      ELSE
        UPDATE KHACHHANG SET KHOILUONGVUOT= 0 WHERE MSKH= id_khachhang;
        PHI_VC:=0;
      END IF;
      
  END UPDATE_KLVUOT;
  /
  
--===================================================================
--===========================Index===================================
--===================================================================	
  
  CREATE INDEX "CHUYENBAY_TRONG_IX" ON "CHUYENBAY" (TO_CHAR("THOIDIEMDI",'DD/MM/YYYY'), "MSTB", "SOGHETRONG");
  
  CREATE INDEX "GHEKHACH_MSKH_IX" ON "GHEKHACH" ("MSKH");
 
  CREATE INDEX "GHENGOI_MSLMB_IX" ON "GHENGOI" ("MSLMB");
    
  CREATE INDEX "KHACHHANG_MSCB_IX" ON "KHACHHANG" ("MSCB");
  
  CREATE INDEX "PHICONG_IX" ON "PHICONG" ("MSNV", "LOAIPHICONG") ;
  
  CREATE INDEX "TIEPVIEN_IX" ON "TIEPVIEN" ("MSNV", "NGOAINGUTHONGTHAO") ;
 
  CREATE INDEX "CHINHANH_IX" ON "CHINHANH" ("MSCN", "TENCHINHANH", "THANHPHO", "QUOCGIA") ; 
 

